﻿CREATE VIEW spm.SsasDateDimWrapper
AS 
/*
 * View to support the SSAS Partition Manager.  See https://SsasPartitionManager.codeplex.com/documentation for full documentation.
 * See comments below.
 *
 * Part of the SSAS Partition Manager sample code which can be downloaded from https://SsasPartitionManager.codeplex.com/
 * 
 * Written by Dr. John Tunnicliffe, independent business intelligence consultant
 * Available for consultancy assignments and speaking engagements
 * 	
 * eMail: john@decision-analytics.co.uk 
 * http://www.decision-analytics.co.uk/
 * http://www.sqlbits.com/Speakers/Dr_John_Tunnicliffe
 * https://www.linkedin.com/in/drjohntunnicliffe
 */ 
SELECT
	/* 
	 * Change this to get the same results from your own date dimension table
	 */
	D.DateKey as DateKey,
	cast(FullDateAlternateKey as date) as ActualDate,

	-- all the following should be strings
	N'[Date].[Date]' as DailyPartitionSliceMember,
	N'&[' + cast(D.DateKey as nvarchar(8)) + N']' as DailyPartitionSliceKey,
	cast(CalendarYear as nvarchar(4)) as CalendarYear,
	cast(CalendarQuarter as nvarchar(1)) as CalendarQuarter, 
	right(N'0' + cast(month(FullDateAlternateKey) as nvarchar(2)),2) as CalendarMonth, 
	right(N'0' + cast(WeekNumberOfYear as nvarchar(2)),2) as WeekNumberOfYear,
	right(N'00' + cast(DayNumberOfYear as nvarchar(3)),3) as DayNumberOfYear
FROM dbo.DimDate D  -- change this to being your local date dimension
GO
